﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using PMS.ENT;
using System.Data.SqlClient;
using PMS.Utilities;


namespace PMS.DAL
{
    public class DepartmentDAL
    {
        #region Common
        private static DepartmentENT CreateDepartmentFromReader(IDataReader reader)
        {
            DepartmentENT item = new DepartmentENT();
            try
            {

                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.DepartmentID.ToString()))) item.DepartmentID = Utility.IntParse(reader[DepartmentColumns.DepartmentID.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.DepartmentName.ToString()))) item.DepartmentName = Utility.StringParse(reader[DepartmentColumns.DepartmentName.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.CreatedBy.ToString()))) item.CreatedBy = Utility.IntParse(reader[DepartmentColumns.CreatedBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.UpdateBy.ToString()))) item.UpdateBy = Utility.IntParse(reader[DepartmentColumns.UpdateBy.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.CreatedDate.ToString()))) item.CreatedDate = Utility.DateTimeParse(reader[DepartmentColumns.CreatedDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.UpdateDate.ToString()))) item.UpdateDate = Utility.DateTimeParse(reader[DepartmentColumns.UpdateDate.ToString()]);
                if (!reader.IsDBNull(reader.GetOrdinal(DepartmentColumns.Isdeleted.ToString()))) item.Isdeleted = Utility.IntParse(reader[DepartmentColumns.Isdeleted.ToString()]);

            }
            catch
            {
                throw;
            }
            return item;
        }
        #endregion

        #region Get
        public static DepartmentENT GetDepartmentByID(int departmentID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentGetByID.ToString());
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, departmentID);

                DepartmentENT department = new DepartmentENT();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    if (reader.Read())
                    {
                        department = CreateDepartmentFromReader(reader);
                    }
                    reader.Close();
                }

                return department;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetDepartmentByID");
            }
        }

        public static DepartmentCollection GetDepartmentList(string DepartmentName,string orderBy, string orderDirection, int page, int pageSize, out int totalRecords)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentGetList.ToString());
                DepartmentCollection listDepartment = new DepartmentCollection();
                database.AddInParameter(dbCommand, "@DepartmentName", DbType.String, DepartmentName);
                database.AddInParameter(dbCommand, "@OrderBy", DbType.String, orderBy);
                database.AddInParameter(dbCommand, "@OrderDirection", DbType.AnsiString, orderDirection);
                database.AddInParameter(dbCommand, "@Page", DbType.Int32, page);
                database.AddInParameter(dbCommand, "@PageSize", DbType.Int32, pageSize);
                database.AddOutParameter(dbCommand, "@TotalRecords", DbType.Int32, 0);

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listDepartment.Add(CreateDepartmentFromReader(reader));
                    }
                    reader.Close();
                }

                totalRecords = (int)database.GetParameterValue(dbCommand, "@TotalRecords");

                return listDepartment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetDepartmentList");
            }
        }

        public static DepartmentCollection GetDepartmentListForCMB()
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentGetListForCMB.ToString());

                DepartmentCollection listDepartment = new DepartmentCollection();

                using (IDataReader reader = database.ExecuteReader(dbCommand))
                {
                    while (reader.Read())
                    {
                        listDepartment.Add(CreateDepartmentFromReader(reader));
                    }
                    reader.Close();
                }

                return listDepartment;
            }
            catch (SqlException)
            {
                throw new DataAccessException("BLM_ERR_GetDepartmentListForCMB");
            }
        }
        #endregion

        #region Insert, Update, Delete

        public static int InsertDepartment(DepartmentENT Department)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentInsert.ToString());
                int DepartmentID = 0;
                database.AddOutParameter(dbCommand, "@DepartmentID", DbType.Int32, Department.DepartmentID);
                database.AddInParameter(dbCommand, "@DepartmentName", DbType.String, Department.DepartmentName);
                database.AddInParameter(dbCommand, "@CreatedBy", DbType.Int32, Department.CreatedBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    DepartmentID = Utility.IntParse(database.GetParameterValue(dbCommand, "@DepartmentID"));
                    return DepartmentID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_InsertDepartment");
            }
        }

        public static int UpdateDepartment(DepartmentENT Department)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentUpdate.ToString());

                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, Department.DepartmentID);
                database.AddInParameter(dbCommand, "@DepartmentName", DbType.String, Department.DepartmentName);
                database.AddInParameter(dbCommand, "@UpdateBy", DbType.Int32, Department.UpdateBy);

                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    int DepartmentID = Utility.IntParse(database.GetParameterValue(dbCommand, "@DepartmentID"));
                    return DepartmentID;
                }
                return -1;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_UpdateDepartment");
            }
        }

        public static int DeleteDepartment(int DepartmentID, int userID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetStoredProcCommand(Procedure.procDepartmentDelete.ToString());
                database.AddInParameter(dbCommand, "@DepartmentID", DbType.Int32, DepartmentID);
                database.AddInParameter(dbCommand, "@UserID", DbType.Int32, userID);
                if (database.ExecuteNonQuery(dbCommand) > 0)
                {
                    return 1;
                }
                return 0;
            }
            catch (Exception)
            {
                throw new DataAccessException("BLM_ERR_DeleteDepartment");
            }
        }
        #endregion
    }
}
